<?php
/**
 * mysql class offer common interface to handling
 * 		the database operation. 
 *
 * @author	Liu xiao<seanliu707@gmail.com>
 * @since	1.0
 */
class Mysql implements Idb {
	
	private $_debug = FALSE;		/*for debug*/
	private $_link = NULL;			/*mysql connect resource*/
	private $_host;					/*connection information*/
	
	/*connected to the database server and do some query work to unify the charset*/
	private function connect() {
		$this->_link = mysqli_connect($this->_host['host'],
				$this->_host['user'], $this->_host['pass'], $this->_host['db'], $this->_host['port']);
		
		if ( $this->_link == FALSE ) die("Error: cannot connected to the database server.");
		
		$_charset = $this->_host['charset'];
		mysqli_query( $this->_link, 'SET NAMES \''.$_charset.'\'');
		mysqli_query( $this->_link, 'SET CHARACTER_SET_CLIENT = \''.$_charset.'\'');
		mysqli_query( $this->_link, 'SET CHARACTER_SET_RESULTS = \''.$_charset.'\'');
	}
	
	public function __construct( &$_host ) {
		$this->_host = &$_host;
	}
	
	/**
	 * Send an query string to the mysql server.
	 * 
	 * @param string $_query
	 * @return	mixed
	 */
	public function query( &$_query ) {
		//connect to the database server as necessary
		if ( $this->_link == NULL ) $this->connect();
		//print the query string for debug	
		if ( $this->_debug ) echo 'query: ', $_query, '<br />';
		return mysqli_query( $this->_link, $_query );
	}
	
	/**
	 * Insert the specified array into the database.
	 * 
	 * @param string $_table the table name
	 * @param array $_array data array
	 * @return	mixed
	 */
	public function insert( $_table, &$_array ) {
		$_fileds = NULL;$_values = NULL;
		foreach ( $_array as $_key => $_val ) {
			$_fileds .= ( $_fileds==NULL ) ? $_key : ',' . $_key;
			$_values .= ( $_values==NULL ) ? '\''.$_val.'\'' : ',\''.$_val.'\'';
		}
		
		if ( $_fileds !== NULL ) {
			$_query = 'INSERT INTO ' . $_table . '(' . $_fileds . ') VALUES(' . $_values . ')';
			if ( $this->query( $_query ) != FALSE ) return mysqli_insert_id( $this->_link );
		}
		return FALSE;
	}
	
	/**
	 * Delete the specified record.
	 * 
	 * @param string $_table the specified table name
	 * @param string $_where
	 * @return	bool
	 */
	public function delete( $_table, $_where ){
		//for safe, where condition must needed
		$_query = "DELETE FROM " . $_table . " WHERE ".$_where;
		if ( $this->query( $_query ) != FALSE )
			return mysqli_affected_rows($this->_link);
		return FALSE;
	}
	
	
	/**
	 * Get a array list from the database.
	 * 
	 * @param string $_query
	 * @param $_type the type of return array
	 * @return	mixed
	 */
	public function getList( $_query, $_type = MYSQLI_ASSOC ){
		$_ret = $this->query( $_query );
		if ( $_ret !== FALSE ) {
			$_result = array();
			while ( ( $_row = mysqli_fetch_array( $_ret, $_type ) ) != FALSE )
				$_result[] = $_row;
			return $_result;
		}
		return FALSE;
	}
	
	/**
	 * Get the array list grouped by specified column.
	 * 
	 * @param string $_query
	 * @param string $group_key
	 * @param string $_type
	 * @return multitype:  array |boolean
	 */
	public function groupList($_query, $group_key, $_type = MYSQLI_ASSOC)
	{
	    $_arr = array();
	    $_ret = $this->query($_query);
	    
	    if ( $_ret !== FALSE ) {
	        //$num = '';
	        while ( ( $row = mysqli_fetch_array( $_ret, $_type ) ) != FALSE ) {
	            if ( key_exists($row[$group_key], $_arr) ) { //如果存在多个相同$group_key,他们对应的值组成一个数组
	                $_old_val = $_arr[$row[$group_key]];
	                //如果原来的值非二维数组，将此元素变成二维数组并将原值写入
	                if ( count($_old_val) == count($_old_val, 1) ) $_arr[$row[$group_key]] = array($_old_val);
	                //如果原来的值为数组，将新的值追加进去
	                $_arr[$row[$group_key]][] = $row;
	            } else {
	                $_arr[$row[$group_key]] = $row;
	            }
	        }
	        return $_arr;
	    }
	    return FALSE;
	}
	
	/**
	 * Update the specified records.
	 * 
	 * @param	$_table
	 * @param	$_array
	 * @param	$_where
	 * @return	mixed
	 */
	public function update( $_table, &$_array, $_where ){
		$_keys = NULL;
		foreach ( $_array as $_key => $_val ) {
			if ( $_keys == NULL ) $_keys .= $_key.'=\''.$_val.'\'';
			else $_keys .= ','.$_key.'=\''.$_val.'\'';
		}
		if ( $_keys !== NULL ) {
			$_query = "UPDATE " . $_table . " SET " . $_keys . " WHERE ".$_where;
			if ( $this->query( $_query ) != FALSE )
				return mysqli_affected_rows($this->_link);
		}
		return FALSE;
	}
	
	
	/**
	 * Get the specified record.
	 *
	 * @param	$_query
	 * @return	mixed
	 */	
	public function getOneRow( $_query, $_type = MYSQLI_ASSOC ) {
		$_ret = $this->query ( $_query );
		if ( $_ret != FALSE ) return mysqli_fetch_array( $_ret, $_type );
		return FALSE;
	}
	
	/**
	 * Get the specified col.
	 * 
	 * @param $_table,$_field_name,$_result_key
	 * @return array/FALSE
	 */
	public function getCol( $_table , $_field_name, $_result_key = null, $_where = null ){
		$_arr = array();
		if ( $_result_key != null ) {
			$query = 'SELECT '.$_result_key.','.$_field_name.' FROM '.$_table;
			if ( $_where != null ) $query .= ' WHERE '.$_where;
				
			$res = $this->query( $query );
			
			if ( $res == FALSE ) return FALSE;
			
			while( ($row = mysqli_fetch_array( $res , MYSQLI_ASSOC )) != FALSE ) {
				if ( key_exists($row[$_result_key], $_arr) ) { //如果存在多个相同$_result_key,他们对应的值组成一个数组
					$_old_val = $_arr[$row[$_result_key]];
					//如果原来的值非数组，将此元素变成数组并将原值写入
					if ( !is_array($_old_val) ) $_arr[$row[$_result_key]] = array($_old_val);
					//如果原来的值为数组，将新的值追加进去
					$_arr[$row[$_result_key]][] = $row[$_field_name];
				} else {
					$_arr[$row[$_result_key]] = $row[$_field_name];
				}
			}
		} else {
			$query = 'SELECT '.$_field_name.' FROM '.$_table;
			if ( $_where != null ) $query .= ' WHERE '.$_where;
			
			$res = $this->query( $query );
			
			if ( $res == FALSE ) return FALSE;
			
			while( ($row = mysqli_fetch_array( $res , MYSQLI_ASSOC )) != FALSE ) {
				$_arr[] = $row[$_field_name];
			}
		}
		return $_arr;
	}
	
	/**
	 * Get row number of the specified query.
	 * 
	 * @param	$_query
	 * @return	int
	 */
	public function getRowNum( $_query ) {
		$_ret = $this->query( $_query );
		if ($_ret != FALSE) return mysqli_num_rows($_ret);
		return 0;
	}
	
	/**
	 * Count the total records.
	 *
	 * @param	$_field
	 * @return	int
	*/
	public function count( $_table, $_field = 0, $_where = NULL ) {
		$_query = 'SELECT count(' . $_field . ') FROM ' . $_table;
		if ( $_where != NULL ) $_query .= ' WHERE ' . $_where;
		if ( ($_ret = $this->getOneRow($_query, MYSQLI_NUM)) != FALSE )
			return $_ret[0];
		return 0;
	}
	
   /**
	 * Get each row number of records which in same conditions.
	 * 
	 * @param $_table,$_refer_filed
	 * @return mixed(bool or array)
	 */
	public function getEachNum( $_table, $_refer_filed ) {
		$_query = 'SELECT count(0) as num,'.$_refer_filed.' FROM '.$_table.' GROUP BY '.$_refer_filed;
		if ( ($_rows = $this->getList($_query) ) == FALSE ) return FALSE;
		
		$_ret = array();
		foreach ( $_rows as $val ) {
			if ( $val[$_refer_filed] != '' )	$_ret[$val[$_refer_filed]] = $val['num'];
		}
		return $_ret;
	}
	
	/**
	* Open debug.
	*/
	public function openDebug() {
		$this->_debug = true;
	}
	
	public function __destruct() {
		if ( $this->_link != NULL ) mysqli_close( $this->_link );
	}
}
